﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.Common
Imports System.Diagnostics ' 用于日志跟踪（可根据实际日志组件替换）

''' <summary>
''' 数据库核心操作类，提供SQL执行、数据查询、参数处理等基础功能
''' 重构说明：优化资源管理、简化代码结构、增强异常处理、规范命名
''' </summary>
Public Class ConfigLib ' 修正类名大写规范
    Private ReadOnly _defaultTimeout As Integer = 1200 ' 默认超时时间（秒）
    Private _connectionString As String ' 数据库连接字符串（私有字段，通过属性访问）
    Private _userInfo As UserInfo ' 操作员信息
    Private _lastErrorMessage As String ' 最后一次错误信息

    ''' <summary>
    ''' 数据库连接字符串
    ''' </summary>
    Public WriteOnly Property ConnectionString As String
        Set(value As String)
            If String.IsNullOrWhiteSpace(value) Then
                Throw New ArgumentException("连接字符串不能为空")
            End If
            _connectionString = value
        End Set
    End Property

    ''' <summary>
    ''' 操作超时时间（秒），默认1200秒
    ''' </summary>
    Public Property CommandTimeout As Integer = _defaultTimeout

    ''' <summary>
    ''' 最后一次操作的错误信息
    ''' </summary>
    Public ReadOnly Property LastErrorMessage As String
        Get
            Return _lastErrorMessage
        End Get
    End Property

    ''' <summary>
    ''' 操作员信息
    ''' </summary>
    Public WriteOnly Property UserInfo As UserInfo
        Set(value As UserInfo)
            _userInfo = value
        End Set
    End Property


    ''' <summary>
    ''' 无参构造函数（使用默认配置）
    ''' </summary>
    Public Sub New()
        ' 初始化默认配置
    End Sub

    ''' <summary>
    ''' 带操作员信息的构造函数
    ''' </summary>
    ''' <param name="userInfo">操作员信息</param>
    Public Sub New(userInfo As UserInfo)
        _userInfo = userInfo
    End Sub

    ''' <summary>
    ''' 创建时间：2017/2/7
    ''' 说明：获取某个模块指定的分组的SQLcmd,返回DataTable  
    ''' </summary>
    ''' <param name="vTableName">模块名</param>
    ''' <param name="vGroupValue">分组名</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns>返回DataTable</returns>
    ''' <remarks></remarks>
    Public Function SqlLib(ByVal vTableName As String, ByVal vGroupValue As String, ByVal vConnection As String) As String
        Dim dt As New DataTable
        Dim strSqlcmd As String '//数据库查询语句

        Dim vSqlcmd As String
        vSqlcmd = ""
        Try
            strSqlcmd = "select * from f_sqllib where col_tablename=@TableName and col_Group=@Group "
            Dim parasql As SqlParameter() = {New SqlParameter("@TableName", vTableName),
                                             New SqlParameter("@group", vGroupValue)}
            dt = F_GetDataTable(strSqlcmd, CommandType.Text, parasql, vConnection)
            vSqlcmd = dt.Rows(0)("col_sqlcmd").ToString
        Catch ex As Exception
            _lastErrorMessage = ex.Message
        End Try
        Return vSqlcmd

    End Function
    ''' <summary>
    ''' 创建时间：2017/7/14
    ''' 说明:获取应用实例下的某个模块字段配置清单，返回DataTable  
    ''' </summary>
    ''' <param name="vSysid">应用实例ID</param>
    ''' <param name="vTableName">模块名</param>
    ''' <param name="vConnection">数据库链接字符串，可选</param>
    ''' <returns>返回记录集DataTable</returns>
    ''' <remarks></remarks>
    Public Function ColumnLib(ByVal vSysid As String, ByVal vTableName As String, Optional vConnection As String = "") As DataTable
        Dim dt As New DataTable
        Dim vSqlcmd As String
        Try
            vSqlcmd = "select col_tablename, col_fieldname, col_byname, col_caption, col_width, col_visible, col_position, col_readonly, col_notes, col_number, col_showsum, col_sqltable, col_cansearch, col_searchorder, col_decimal, col_checkbox, col_button, col_picture ,col_windows from f_ColumnLib where col_notes='1' and Sysid=@Sysid and Col_TableName=@TableName ORDER BY col_Position"
            If Len(vSysid) = 0 Then
                Return ColumnLib(vTableName, vConnection)
            Else
                Dim parasql As SqlParameter() = {New SqlParameter("@TableName", vTableName),
                                                 New SqlParameter("@sysid", vSysid)}
                dt = F_GetDataTable(vSqlcmd, CommandType.Text, parasql, vConnection)
            End If

        Catch ex As Exception
            _lastErrorMessage = ex.Message
        End Try
        Return dt
    End Function

    ''' <summary>
    ''' 创建时间：2017/7/14
    ''' 说明:获取应用实例下的某个模块字段配置清单，返回DataTable  
    ''' </summary>
    ''' <param name="vTableName">模块名</param>
    ''' <param name="vConnection">数据库链接字符串，可选</param>
    ''' <returns>返回记录集DataTable</returns>
    ''' <remarks></remarks>
    Public Function ColumnLib(ByVal vTableName As String, Optional vConnection As String = "") As DataTable
        Dim dt As New DataTable
        Dim vSqlcmd As String
        Try
            vSqlcmd = "select col_tablename, col_fieldname, col_byname, col_caption, col_width, col_visible, col_position, col_readonly, col_notes, col_number, col_showsum, col_sqltable, col_cansearch, col_searchorder, col_decimal, col_checkbox, col_button, col_picture ,col_windows from f_ColumnLib where col_notes='1'  and Col_TableName=@TableName ORDER BY col_Position"
            Dim parasql As SqlParameter() = {New SqlParameter("@TableName", vTableName)}
            dt = F_GetDataTable(vSqlcmd, CommandType.Text, parasql, vConnection)

        Catch ex As Exception
            _lastErrorMessage = ex.Message
        End Try

        Return dt
    End Function
    ''' <summary>
    ''' 创建时间：2017/5/26
    ''' 说明：获取需要合计的列项清单  
    ''' </summary>
    ''' <param name="vTableName">模块名</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns>返回一个记录集DataTable</returns>
    ''' <remarks></remarks>
    Public Function ColumnIsSum(ByVal vTableName As String, ByVal vConnection As String) As DataTable
        Dim dt As New DataTable
        Dim strSqlcmd As String
        dt.Clear()
        Try
            strSqlcmd = "select col_fieldname,col_caption,col_Position from f_ColumnLib where Col_TableName=@TableName and col_showsum='1' order By col_Position "
            Dim parasql As SqlParameter() = {New SqlParameter("@TableName", vTableName)}
            dt = F_GetDataTable(strSqlcmd, CommandType.Text, parasql, vConnection)
        Catch ex As Exception
            _lastErrorMessage = ex.Message
        End Try
        Return dt
    End Function
    ''' <summary>
    ''' 执行SQL查询，返回DataTable
    ''' </summary>
    ''' <param name="sql">SQL语句或存储过程名</param>
    ''' <param name="commandType">命令类型（文本/存储过程）</param>
    ''' <param name="parameters">SQL参数数组（可选）</param>
    ''' <param name="connectionString">连接字符串（可选，默认使用类中设置的连接字符串）</param>
    ''' <returns>查询结果DataTable</returns>
    Public Function F_GetDataTable(
        sql As String,
        commandType As CommandType,
        Optional parameters As SqlParameter() = Nothing,
        Optional connectionString As String = ""
    ) As DataTable
        ' 参数验证
        If String.IsNullOrWhiteSpace(sql) Then
            Throw New ArgumentException("SQL语句不能为空", NameOf(sql))
        End If

        ' 确定连接字符串（优先使用方法参数，其次使用类中设置的）
        Dim connStr = If(String.IsNullOrWhiteSpace(connectionString), _connectionString, connectionString)
        If String.IsNullOrWhiteSpace(connStr) Then
            Throw New InvalidOperationException("未设置数据库连接字符串")
        End If

        Using conn As New SqlConnection(connStr) ' Using自动释放连接
            Try
                conn.Open()
                ' 执行查询并返回数据集
                Dim ds = ExecuteDataset(conn, Nothing, commandType, sql, parameters)
                Return If(ds.Tables.Count > 0, ds.Tables(0), New DataTable())
            Catch ex As Exception
                LogError("GetDataTable执行失败", ex, sql, parameters)
                _lastErrorMessage = ex.Message
                Throw ' 重新抛出异常，让上层处理
            End Try
        End Using
    End Function

    ''' <summary>
    ''' 创建时间： 2017/3/18
    ''' 功能：执行sql命令，返回影响的记录数，执行失败返回-2      
    ''' </summary>
    ''' <param name="vCommandText">SQL查询语句</param>
    ''' <param name="vCommandType">SQL命令类型</param>
    ''' <param name="sqlparameters">参数数组</param>
    ''' <param name="vConnection">数据库链接字符串，可选</param>
    ''' <returns>执行是否成功，执行失败返回异常值-2</returns>
    ''' <remarks></remarks>
    Public Overloads Function F_UpdateSQLCommand(ByVal vCommandText As String, ByVal vCommandType As CommandType, ByVal sqlparameters As SqlParameter(), Optional vConnection As String = "") As Integer

        Dim cn As New SqlConnection(vConnection)
        Try
            cn.Open()
            Return ExecuteNonQuery(cn, vCommandType, vCommandText, sqlparameters)
        Catch ex As Exception
            _lastErrorMessage = "更新语句失败，失败原因:" + ex.Message
            Return -2
        End Try

    End Function
    ''' <summary>
    ''' 创建时间：2017/4/12
    ''' 说明：执行SQL命令，返回影响的记录数,执行错误返回-2     
    ''' </summary>
    ''' <param name="vSqlcon">sqlconnection链接变量</param>
    ''' <param name="vCommandType">命令类型</param>
    ''' <param name="vCommandText">命令执行语句</param>
    ''' <param name="vSqlpara">参数数组</param>
    ''' <returns>执行是否成功，执行失败返回异常值-2</returns>
    ''' <remarks>程序集内部调用</remarks>
    Friend Overloads Function ExecuteNonQuery(ByVal vSqlcon As SqlConnection,
                                              ByVal vCommandType As CommandType,
                                              ByVal vCommandText As String,
                                              ByVal vSqlpara As SqlParameter()) As Integer
        Dim cmd As New SqlCommand
        Dim intsuc As Integer

        Try
            '//配置SQLcommand
            PrepareCommand(cmd, vSqlcon, Nothing, vCommandType, vCommandText, vSqlpara)
            '//执行
            intsuc = cmd.ExecuteNonQuery()
            '//清理参数
            cmd.Parameters.Clear()
            '//释放连接池
            If (ConnectionState.Closed <> vSqlcon.State) Then
                vSqlcon.Close()
                vSqlcon = Nothing '//销毁连接
            End If
        Catch ex As Exception
            LogError("ExecuteDataset执行失败", ex, vCommandText, vSqlpara)
            Throw
        Finally
            cmd.Parameters.Clear() ' 清理参数
        End Try
        Return intsuc
    End Function

    ''' <summary>
    ''' 执行SQL命令，返回DataSet
    ''' </summary>
    ''' <param name="connection">数据库连接</param>
    ''' <param name="transaction">事务（可选）</param>
    ''' <param name="commandType">命令类型</param>
    ''' <param name="sql">SQL语句或存储过程名</param>
    ''' <param name="parameters">SQL参数数组（可选）</param>
    ''' <returns>查询结果DataSet</returns>
    Private Function ExecuteDataset(
        connection As SqlConnection,
        transaction As SqlTransaction,
        commandType As CommandType,
        sql As String,
        Optional parameters As SqlParameter() = Nothing
    ) As DataSet
        ' 参数验证
        If connection Is Nothing Then
            Throw New ArgumentNullException(NameOf(connection), "数据库连接不能为空")
        End If
        If String.IsNullOrWhiteSpace(sql) Then
            Throw New ArgumentException("SQL语句不能为空", NameOf(sql))
        End If

        Using cmd As New SqlCommand() ' Using自动释放命令
            Try
                ' 准备命令（配置连接、事务、参数等）
                PrepareCommand(cmd, connection, transaction, commandType, sql, parameters)

                ' 执行查询并填充数据集
                Using adapter As New SqlDataAdapter(cmd)
                    Dim ds As New DataSet()
                    adapter.Fill(ds)
                    Return ds
                End Using
            Catch ex As Exception
                LogError("ExecuteDataset执行失败", ex, sql, parameters)
                Throw
            Finally
                cmd.Parameters.Clear() ' 清理参数
            End Try
        End Using
    End Function


    ''' <summary>
    ''' 准备SqlCommand（配置连接、事务、命令类型、参数等）
    ''' </summary>
    ''' <param name="cmd">SqlCommand实例</param>
    ''' <param name="connection">数据库连接</param>
    ''' <param name="transaction">事务（可选）</param>
    ''' <param name="commandType">命令类型</param>
    ''' <param name="sql">SQL语句或存储过程名</param>
    ''' <param name="parameters">SQL参数数组（可选）</param>
    Private Sub PrepareCommand(
        cmd As SqlCommand,
        connection As SqlConnection,
        transaction As SqlTransaction,
        commandType As CommandType,
        sql As String,
        Optional parameters As SqlParameter() = Nothing
    )
        ' 基础配置
        cmd.Connection = connection
        cmd.CommandText = sql
        cmd.CommandType = commandType
        cmd.CommandTimeout = CommandTimeout ' 使用设置的超时时间

        ' 关联事务（如果有）
        If transaction IsNot Nothing Then
            cmd.Transaction = transaction
        End If

        ' 添加参数（如果有）
        If parameters IsNot Nothing AndAlso parameters.Length > 0 Then
            AttachParameters(cmd, parameters)
        End If
    End Sub


    ''' <summary>
    ''' 向SqlCommand添加参数
    ''' </summary>
    ''' <param name="cmd">SqlCommand实例</param>
    ''' <param name="parameters">参数数组</param>
    Private Sub AttachParameters(cmd As SqlCommand, parameters As SqlParameter())
        For Each param As SqlParameter In parameters
            If param Is Nothing Then
                Continue For ' 跳过空参数
            End If

            ' 处理输出参数
            If param.Direction = ParameterDirection.InputOutput AndAlso param.Value Is Nothing Then
                param.Value = DBNull.Value
            End If

            cmd.Parameters.Add(param)
        Next
    End Sub


    ''' <summary>
    ''' 错误日志记录（可替换为实际日志组件）
    ''' </summary>
    ''' <param name="operation">操作描述</param>
    ''' <param name="ex">异常对象</param>
    ''' <param name="sql">执行的SQL语句</param>
    ''' <param name="parameters">参数列表</param>
    Private Sub LogError(operation As String, ex As Exception, sql As String, parameters As SqlParameter())
        Dim paramInfo = If(parameters Is Nothing, "无参数",
            String.Join("; ", parameters.Select(Function(p) $"{p.ParameterName}={p.Value}")))

        Dim logMessage = $"{operation}：{ex.Message}{Environment.NewLine}SQL：{sql}{Environment.NewLine}参数：{paramInfo}"
        ' 实际项目中可替换为日志框架（如NLog、Log4Net）
        Debug.WriteLine(logMessage)
        ' 若需要持久化日志，可在此处添加文件写入或数据库记录逻辑
    End Sub
    ''' <summary>
    ''' 创建时间：2017/7/13
    ''' 说明：写日志到数据库中。返回操作失败=-2
    ''' </summary>
    ''' <param name="vLogDate">日志时间，格式yyyy-mm-dd hh:mm:ss</param>
    ''' <param name="vBillType">操作类型</param>
    ''' <param name="vOperator">操作人</param>
    ''' <param name="vMod">模块名</param>
    ''' <param name="vOpContent">操作内容</param>
    ''' <param name="vStatus">日志反馈状态。1=操作成功，0=操作失败</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overridable Function WriteLog(ByVal vLogDate As String, ByVal vBillType As String, ByVal vOperator As String, ByVal vMod As String, ByVal vOpContent As String, ByVal vStatus As String, ByVal vConnection As String) As Integer
        Dim intsuc As Integer = -2
        Dim strsqlcmd As String
        strsqlcmd = "Insert into s_log(BillDate, BillType, Operator, Mod, OPContent,  Status )"
        strsqlcmd = strsqlcmd + "select @LogDate, @BillType, @Operator, @Mod, @OPContent, @Status "
        Try
  

            Dim vDbPara As DbParameter() = {New SqlParameter("@LogDate", vLogDate),
            New SqlParameter("@BillType", vBillType),
            New SqlParameter("@Operator", vOperator),
            New SqlParameter("@Mod", vMod),
            New SqlParameter("@OpContent", vOpContent),
            New SqlParameter("@Status", vStatus)}
            intsuc = F_UpdateSQLCommand(strsqlcmd,CommandType.Text, vDbPara, vConnection)

        Catch ex As Exception
            intsuc = -2
            '//如果未写入系统，在本地错误日志
            LogError(MUser.UserName+"日志写入s_log表错误",ex,strsqlcmd,Nothing )
        End Try
        Return intsuc
    End Function

#Region "用户权限模块相关函数"



    ''' <summary>
    ''' 创建时间：2017/5/19
    ''' 说明：返回用户某个模块的权限清单    
    ''' </summary>
    ''' <param name="vUserName">用户名/用户id</param>
    ''' <param name="vModuleName">模块名</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns>返回一个记录集DataTable</returns>
    ''' <remarks></remarks>
    Public Overridable Function UserModulePower(ByVal vUserName As String, ByVal vModuleName As String, ByVal vConnection As String) As DataTable
        Dim sqlpara As SqlParameter() = {New SqlParameter("@UserName", vUserName),
                                         New SqlParameter("@ModuleName", vModuleName)}
        Return UserModulePower(sqlpara, vConnection)
    End Function

    ''' <summary>
    ''' 创建时间：2017/5/19
    ''' 说明：返回用户某个模块的权限清单    
    ''' </summary>
    ''' <param name="vSqlpara">参数数组，传递(@UserName,@ModuleName)</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns>返回一个记录集DataTable</returns>
    ''' <remarks>传递参数数组用户名/用户id，模块名</remarks>
    Public Overridable Function UserModulePower(ByVal vSqlpara As SqlParameter(), ByVal vConnection As String) As DataTable
        Dim dt As New DataTable
        Dim strsqlcmd As String
        dt.Clear()
        Try
            '2018.4.22调整，PermissionCode=@ModuleName
            strsqlcmd = "select * from v_m_qxlist where (PermissionCode=@ModuleName or m_module=@ModuleName) and (UserName=@UserName or UserID=@UserName) "
            dt = F_GetDataTable(strsqlcmd, CommandType.Text, vSqlpara, vConnection)
        Catch ex As Exception
            _lastErrorMessage = ex.Message
        End Try

        Return dt
    End Function
#End Region
End Class